********Clean HH income and assets data from IFLS5********
*Note: Income data is sprinkled across several books, so need to compile

cd "$ifls5rawdata"

***Income and transfers into household

*Initialize
use b2_cov.dta, clear
keep hhid14 pid14

tempfile hhbalsheet14
save "`hhbalsheet14'", replace


*Labor income and profits (from individual sections of survey)
use b3a_tk2.dta, clear
keep hhid14 pid14 tk25a2 tk25a2b tk25b2 tk25b2b tk26a3 tk26b3
collapse (sum) tk25a2 tk25a2b tk25b2 tk25b2b tk26a3 tk26b3, by(hhid14)
egen totlabin = rowtotal (tk25a2 tk25a2b tk25b2 tk25b2b)
la var totlabin "total yearly labor income from all HH members"
egen totprof = rowtotal (tk26a3 tk26b3)
la var totprof "Total yearly profit from all HH members"
*It's unclear whether or not total 
keep hhid14 totlabin totprof
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergelip)
save "`hhbalsheet14'", replace


*Farm profit
use b2_ut1.dta, clear
keep ut07 ut08 ut01 ut00a hhid14
replace ut07 = 0 if ut01 == 3 & ut00a == 3
replace ut08 = 0 if ut01 == 3 & ut00a == 3
gen farmprof = ut07 - ut08
la var farmprof "Yearly farm profit"
rename ut07 farmrev
rename ut08 farmexp
drop ut00a ut01
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergefp)
save "`hhbalsheet14'", replace


*Non-farm business profit
use b2_nt2.dta, clear
keep nt02 nt03 nt09 hhid14
replace nt03=0 if nt03==.
replace nt09=0 if nt09==.
replace nt09 = nt09*nt03/100 if nt02!=1
drop nt03
collapse (sum) nt09, by(hhid14)
rename nt09 busprof
la var busprof "Yearly non-farm business profit"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergebp)
replace busprof = 0 if busprof==.
save "`hhbalsheet14'", replace



*Income from rental/interest of hh assets
use b2_hr1.dta, clear
collapse (sum) hr05, by(hhid14)
replace hr05 = 0 if hr05==.
rename hr05 hhassinc
la var hhassinc "Yearly total income from household assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeai)
save "`hhbalsheet14'", replace

*Income from rental/interest of farm assets
use b2_ut4, clear
collapse (sum) ut14, by(hhid14)
rename ut14 fassinc
la var fassinc "Yearly total income from rent/lease/profit sharing for farm assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergefai)
save "`hhbalsheet14'", replace


*Income from rental/interest of non-farm business assets
use b2_nt2.dta, clear
replace nt26 = nt26*nt03/100 if nt02!=1
collapse (sum) nt26, by(hhid14)
rename nt26 nfassinc
la var nfassinc "Yearly total income from rent/lease/profit sharing for nonfarm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergenfai)
save "`hhbalsheet14'", replace

*Income from sales of non-farm business assets
use b2_nt2.dta, clear
replace nt25 = nt25*nt03/100 if nt02!=1
collapse (sum) nt25, by(hhid14)
rename nt25 nfasssale
la var nfasssale "Income from sale of nonfarm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergesnfai)
save "`hhbalsheet14'", replace

*Income from sales of farm assets
use b2_ut4, clear
collapse (sum) ut13, by(hhid14)
rename ut13 fasssale
la var fasssale "Income from sale of farm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergesfai)
save "`hhbalsheet14'", replace

*Income from sales of household assets
use b2_hr2, clear
collapse (sum) hr17, by(hhid14)
rename hr17 hhasssale
la var hhasssale "Income from sale of household nonbusiness assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeshhai)
save "`hhbalsheet14'", replace

*Non-labor Income from other sources
use b2_hi.dta, clear
collapse (sum) hi14, by(hhid14)
rename hi14 othinc 
la var othinc "Yearly total income from other sources-scholarships,insurance,lottery"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeoi)
save "`hhbalsheet14'", replace

*Transfers into HH
use b3b_ba1, clear
egen trinpar = rowtotal(ba22a_p ba22d_p ba22a_m ba22d_m ba22a_pm ba22d_pm ba22v_p ba22v_m ba22v_pm)
collapse (sum) trinpar, by(hhid14)
la var trinpar "Transfers in to hh from non-coresident parents"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeinpar)
save "`hhbalsheet14'", replace 

use b3b_ba4, clear
egen trinsib = rowtotal(ba57a ba57d ba57v)
collapse (sum) trinsib, by(hhid14)
la var trinsib "Transfers in to hh from non-coresident siblings"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeinsib)
save "`hhbalsheet14'", replace 

use b3b_ba6, clear
collapse (sum) ba90a ba90d ba90v, by(hhid14)
egen trinchi = rowtotal(ba90a ba90d ba90v)
drop ba90a ba90d ba90v
la var trinchi "Transfers in to hh from non-coresident children"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeinchi)
save "`hhbalsheet14'", replace

*Government cash transfers
use b1_ksr1.dta, clear
keep ksr3type ksr21 hhid14
reshape wide ksr21, i(hhid14) j(ksr3type) string
egen gcashtr = rowtotal(ksr21A ksr21B ksr21C)
replace gcashtr = 0 if gcashtr==.
drop ksr21A ksr21B ksr21C
la var gcashtr "Total yearly government cash transfers"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergegctr)
save "`hhbalsheet14'", replace

*Government rice subsidy transfers
use b1_ksr2.dta, clear
keep ksr24a ksr26b_kg ksr26b_rp ksr29 ksr31 ksr32 hhid14
replace ksr29 = 0 if ksr29 == .

*Dealing with unreasonable amounts
gen dum = ksr26b_kg/ksr29
replace dum = 24 if dum > 24  
replace ksr31 = ksr31*(-1)*dum
replace ksr32 = ksr32*dum
egen raskin = rowtotal (ksr26b_rp ksr31 ksr32)

*Removing "negative" subsidy
replace raskin = 0 if raskin < 0
la var raskin "Imputed total yearly Raskin subsidy (rice and cash)"
keep hhid14 raskin
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergersub)
save "`hhbalsheet14'", replace



*Other subsidized government transfers through market operations
use b1_ksr3.dta, clear
keep ksr2type hhid14 ksr40 ksr42 ksr43 ksr45
reshape wide ksr4*, i(hhid14) j(ksr2type) string

*Sugar
replace ksr40D = 0 if ksr40D==.
replace ksr42D = 0 if ksr42D==.
replace ksr43D = 0 if ksr43D==.
replace ksr45D = 0 if ksr45D==.
gen dum1 = 0

*Calculating the market subsidy from last transaction and imputing for the whole year
replace dum1 = ksr42D/ksr43D*(ksr45D-ksr43D)*ksr40D if ksr43D!=0
replace dum1 = 0 if dum1 < 0

*Cooking oil
replace ksr40J = 0 if ksr40J==.
replace ksr42J = 0 if ksr42J==.
replace ksr43J = 0 if ksr43J==.
replace ksr45J = 0 if ksr45J==.
gen dum2 = 0
replace dum2 = ksr42J/ksr43J*(ksr45J-ksr43J)*ksr40J if ksr43J!=0
replace dum2 = 0 if dum2 < 0

*Kerosene
replace ksr40K = 0 if ksr40K==.
replace ksr42K = 0 if ksr42K==.
replace ksr43K = 0 if ksr43K==.
replace ksr45K = 0 if ksr45K==.
gen dum3 = 0
replace dum3 = ksr42K/ksr43K*(ksr45K-ksr43K)*ksr40K if ksr43K!=0
replace dum3 = 0 if dum3 < 0

*Add up
gen othmosub = dum1+dum2+dum3
la var othmosub "imputed total yearly market operations non-rice transfers"
keep othmosub hhid14
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeothsub)
save "`hhbalsheet14'", replace

*NGO transfers into HH in cash and kind
use b1_ksr4.dta, clear
keep ksr5type hhid14 ksr50
reshape wide ksr50, i(hhid14) j(ksr5type) string
egen ngotr = rowtotal(ksr50A ksr50B)
la var ngotr "total non-government transfers in cash and kind in the last 4 weeks" 
keep ngotr hhid14
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergengtr)
save "`hhbalsheet14'", replace

*Total yearly income from all sources
use "`hhbalsheet14'", clear
egen totinc = rowtotal(totlabin farmprof busprof hhassinc fassinc nfassinc hhasssale nfasssale fasssale othinc trinpar trinsib trinchi gcashtr raskin othmosub ngotr)
la var totinc "Total household yearly income from all sources"
save "`hhbalsheet14'", replace

***Savings and investment 

*Household (non-business) asset purchases
use b2_hr2, clear
collapse (sum) hr16, by(hhid14)
rename hr16 hhassbuy
la var hhassbuy "Purchase of household nonbusiness assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeshhap)
save "`hhbalsheet14'", replace

*Non-farm business asset purchases
use b2_nt2.dta, clear
replace nt24 = nt24*nt03/100 if nt02!=1
collapse (sum) nt24, by(hhid14)
rename nt24 nfassbuy
la var nfassbuy "Purchases of nonfarm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergesnfap)
save "`hhbalsheet14'", replace

*Farm asset purchases
use b2_ut4, clear
collapse (sum) ut12, by(hhid14)
rename ut12 fassbuy
la var fassbuy "Purchases of farm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergesfap)
save "`hhbalsheet14'", replace

*HH contributions to Arisan (imputed from monthly)
use b1_ks2, clear
keep if ks2type=="F2"
keep ks06 hhid14
replace ks06=ks06*12
rename ks06 arisan
la var arisan "imputed (from monthly) yearly contribution to Arisan"
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergearis)
save "`hhbalsheet14'", replace

*Total savings from all sources
use "`hhbalsheet14'", clear
egen totsav = rowtotal(hhassbuy fassbuy nfassbuy arisan)
la var totsav "Total household yearly savings and investment from all sources (asset purchases + arisan contributions)"
save "`hhbalsheet14'", replace

***Borrowing

use b2_bh.dta, clear
keep hhid14 bh10

rename bh10 totbor

merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergelend)
replace totbor = 0 if totbor==.
la var totbor "Total yearly borrowing by HH from non-friends or family"
save "`hhbalsheet14'", replace

***Assets


*Farm business assets
use b2_ut4, clear
egen assdum = rowtotal(ut11_*)
collapse (sum) assdum, by(hhid14)
rename assdum totfarmass
la var totfarmass "Total farm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergetfass)
replace totfarmass=0 if totfarmass==.
save "`hhbalsheet14'", replace

*By different asset categories
use b2_ut4, clear
keep hhid14 ut11_*
collapse (sum) ut11_*, by (hhid14)
drop ut11_j
rename ut11_a fassland
la var fassland "Farm asset total market value: Farm land"
rename ut11_d1 fasspoul
la var fasspoul "Farm asset total market value: Poultry"
rename ut11_d2 fassliv
la var fassliv "Farm asset total market value: Livestock/fish pond"
rename ut11_b fasscc
la var fasscc "Farm asset total market value: Hard stem plants - coconut, coffee, cloves, rubber etc. (cash crops)"
rename ut11_c fassbuil
la var fassbuil "Farm asset total market value: house/building used for farm business"
rename ut11_e fassveh
la var fassveh "Farm asset total market value: vehicles"
rename ut11_f fasstract
la var fasstract "Farm asset total market value: Tractor"
rename ut11_f1 fassirr
la var fassirr "Farm asset total market value: irrigation equipment"
rename ut11_g fassheav
la var fassheav "Farm asset total market value: heavy equipment (farming machine, generator, etc.)"
rename ut11_h fasstool
la var fasstool "Farm asset total market value: Small tools"
rename ut11_h1 fassfish
la var fassfish "Farm asset total market value: Fishing Equipment"
rename ut11_i fassoth
la var fassoth "Farm asset total market value: Other"
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergetfassdet)
replace fassland=0 if fassland==.
replace fasspoul=0 if fasspoul==.
replace fassliv=0 if fassliv==.
replace fasscc=0 if fasscc==.
replace fassbuil=0 if fassbuil==.
replace fassveh=0 if fassveh==.
replace fasstract=0 if fasstract==.
replace fassirr=0 if fassirr==.
replace fassheav=0 if fassheav==.
replace fassoth=0 if fassoth==.
save "`hhbalsheet14'", replace



*Non-farm business assets
use b2_nt2.dta, clear
keep nt_num hhid14 nt10a nt10b nt10c1 nt10c4 nt10h
collapse (sum) nt10*, by(hhid14)
replace nt10a = 0 if nt10a==.
replace nt10b = 0 if nt10b==.
replace nt10c1 = 0 if nt10c1==.
replace nt10c4 = 0 if nt10c4==.
replace nt10h = 0 if nt10h==.
egen totnfass = rowtotal(nt10a nt10b nt10c1 nt10c4 nt10h)
collapse (sum) totnfass nt10a nt10b nt10c1 nt10c4 nt10h, by(hhid14)
rename nt10a nfassland
la var nfassland "Nonfarm asset total market value: Land"
rename nt10b nfassbuil
la var nfassbuil "Nonfarm asset total market value: Building"
rename nt10c1 nfassfwveh
la var nfassfwveh "Nonfarm asset total market value: Four-wheel motor vehicle"
rename nt10c4 nfassothveh
la var nfassothveh "Nonfarm asset total market value: Other Vehicles"
rename nt10h nfassoth
la var nfassoth "Nonfarm asset total market value: Other"
la var totnfass "Total nonfarm business assets"
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergetnfass)
replace nfassland=0 if nfassland==.
replace nfassbuil=0 if nfassbuil==.
replace nfassfwveh=0 if nfassfwveh==.
replace nfassothveh=0 if nfassothveh==.
replace nfassoth=0 if nfassoth==.
replace totnfass=0 if totnfass==.
save "`hhbalsheet14'", replace


*Non-business (household) assets
use b2_hr1.dta, clear
keep hrtype hr02_* hr06 hr07 hhid14
egen dum = rowtotal (hr02_*)
replace dum = dum*hr07/100 if hr06==3
collapse (sum) dum, by(hhid14)
rename dum tothhass
la var tothhass "Total nonfarm nonbusiness (household) assets"
keep hhid14 tothhass
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhass)
save "`hhbalsheet14'", replace

*Detailed asset categories
use b2_hr1.dta, clear
keep hrtype hr02_* hr07 hr06 hhid14
replace hr02_a = hr02_a*hr07/100 if hr06==3
replace hr02_b = hr02_b*hr07/100 if hr06==3
replace hr02_c = hr02_c*hr07/100 if hr06==3
replace hr02_d1 = hr02_d1*hr07/100 if hr06==3
replace hr02_d2 = hr02_d2*hr07/100 if hr06==3
replace hr02_d3 = hr02_d3*hr07/100 if hr06==3
replace hr02_e = hr02_e*hr07/100 if hr06==3
replace hr02_f = hr02_f*hr07/100 if hr06==3
replace hr02_g = hr02_g*hr07/100 if hr06==3
replace hr02_h = hr02_h*hr07/100 if hr06==3
replace hr02_j = hr02_j*hr07/100 if hr06==3
replace hr02_k1 = hr02_k1*hr07/100 if hr06==3
replace hr02_k2 = hr02_k2*hr07/100 if hr06==3
collapse (sum) hr02_*, by (hhid14)


rename hr02_a hhasshouse
la var hhasshouse "Household asset total market value: House and Land occupied by household"
rename hr02_b hhassothhouse
la var hhassothhouse "Household asset total market value: Other House and Land"
rename hr02_c hhassland
la var hhassland "Household asset total market value: Land (Not used for farm or nonfarm business)"
rename hr02_d1 hhasspoul
la var hhasspoul "Household asset total market value: poulty"
rename hr02_d2 hhassliv
la var hhassliv "Household asset total market value: livestock/fishpond"
rename hr02_d3 hhasscc
la var hhasscc "Household asset total market value: Hard stem plants (not used for farm or business)"
rename hr02_e hhassveh
la var hhassveh "Household asset total market value: Vehicles"
rename hr02_f hhassapp
la var hhassapp "Household asset total market value: Household Appliances"
rename hr02_g hhasssav
la var hhasssav "Household asset total market value: Savings/certificates of Deposit/Stocks"
rename hr02_h hhassrec
la var hhassrec "Household asset total market value: Receivables"
rename hr02_j hhassjew
la var hhassjew "Household asset total market value: Jewelry"
rename hr02_k1 hhassfur
la var hhassfur "Household asset total market value: Household Furniture and Utensils"
rename hr02_k2 hhassoth
la var hhassoth "Household asset total market value: Other"


merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhassdet)
save "`hhbalsheet14'", replace

*Total assets
use "`hhbalsheet14'", clear
egen totass = rowtotal(totfarmass totnfass tothhass)
la var totass "total HH assets all kinds"
save "`hhbalsheet14'", replace

***Consumption

*HH total food consumption (imputed from weekly) (includes tobacco/betel)
use b1_ks1, clear
collapse (sum) ks02 ks03, by(hhid14)
rename ks02 wexpout
la var wexpout "Weekly expenditure by hh outside home to purchase food (+tobacco and betel)"
rename ks03 wexpin
la var wexpout "Weekly consumption of self-produced/received food"
egen hhfoodcons = rowtotal(wexpout wexpin)
replace hhfoodcons = hhfoodcons*52
la var hhfoodcons "Imputed (*52) total yearly consumption of food in hh (includes tobacco and betel nut)"
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhwcons)
save "`hhbalsheet14'", replace

*HH total nonfood consumption (imputed from monthly) - does not include transfers outside the home
use b1_ks2, clear
drop if ks2type=="F2"
drop if ks2type=="G"
keep ks06 hhid14
collapse (sum) ks06, by(hhid14)
replace ks06=ks06*12
rename ks06 nfconsdum1
la var nfconsdum1 "yearly nonfood consumption (imputed from monthly) dummy" 
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhmcons)
save "`hhbalsheet14'", replace

use b1_ks3, clear
drop if ks3type == "G"
egen nfconsdum2 = rowtotal(ks08 ks09a)
collapse (sum) nfconsdum2, by(hhid14)
la var nfconsdum2 "yearly irregular nonfood consumption dummy" 
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhycons)
save "`hhbalsheet14'", replace


*Education expenditures
use b1_ks0, clear
egen edexp = rowtotal(ks10aa ks10ab ks11aa ks11ab ks12aa ks12ab ks12bb)
la var edexp "Yearly HH expenditures on education"
keep hhid14 edexp
merge 1:1 hhid14 using "`hhbalsheet14'", gen (_mergehhed)
save "`hhbalsheet14'", replace

*Food transfers to outside
use b1_ks0, clear
keep hhid14 ks04b
replace ks04b = ks04b*12
rename ks04b foodtrout
la var foodtrout "Imputed yearly (*12) hh food transfer out of hh"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeftr)
save "`hhbalsheet14'", replace

*Regular nonfood transfers to outside the HH
use b1_ks2, clear
keep ks06 hhid14 ks2type
keep if ks2type=="G"
drop ks2type
rename ks06 rnftrout
replace rnftrout = rnftrout*12
la var rnftrout "Imputed (*12) total yearly regular non-food transfers outside the HH" 
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergenftr)
save "`hhbalsheet14'", replace

*Irregular nonfood transfers outside the household
use b1_ks3, clear
keep if ks3type=="G"
keep hhid14 ks08
rename ks08 irnftrout
la var irnftrout "Total yearly irregular non-food transfers outside the HH" 
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeinftr)
save "`hhbalsheet14'", replace

*Transfers out of HH
use b3b_ba1, clear
egen troutpar = rowtotal(ba20a_p ba20d_p ba20a_m ba20d_m ba20a_pm ba20d_pm ba20v_m ba20v_p ba20v_pm)
collapse (sum) troutpar, by(hhid14)
la var troutpar "Transfers out of hh to non-coresident parents"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeoutpar)
save "`hhbalsheet14'", replace 

use b3b_ba4, clear
egen troutsib = rowtotal(ba55a ba55d ba55v)
collapse (sum) troutsib, by(hhid14)
la var troutsib "Transfers out of hh to non-coresident siblings"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeoutsib)
save "`hhbalsheet14'", replace 

use b3b_ba6, clear
collapse (sum) ba88a ba88d ba88v, by(hhid14)
egen troutchi = rowtotal(ba88a ba88d ba88v)
drop ba88a ba88d ba88v
la var troutchi "Transfers out of hh to non-coresident children"
merge 1:1 hhid14 using "`hhbalsheet14'", gen(_mergeoutchi)
save "`hhbalsheet14'", replace

*Total comsumption
use "`hhbalsheet14'", clear
egen totcons = rowtotal(hhfoodcons nfconsdum1 nfconsdum2 edexp foodtrout rnftrout irnftrout troutpar troutsib troutchi)
la var totcons "total HH yearly consumption, expenditures, and transfers out of the household"
drop _merge*
save "`hhbalsheet14'", replace

